﻿using AutoMapper;
using MediatR;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using S.Common.ApiResult;
using S.Transfer.Domain;
using S.Transfer.Domain.Model;
using S.Transfer.Infrastructure;
using S.Transfer.Infrastructure.Interface.Transfer;
using S.Transfer.Read.API.Application.Command.UserCommand;
using S.Transfer.Read.API.Extensions.Dto;
using System.Text;
using static NPOI.HSSF.Util.HSSFColor;

namespace S.Transfer.Read.API.Application.CommandHandlers.UserCommandHandler
{
    public class TransferShowlCommondHandler : IRequestHandler<TransferShowlCommond, Pager<SheepShowDto>>, IRequestHandler<TransferCommond, List<SheepFromDto>>,IRequestHandler<TransFerByDateCommond, SheepRescission>,IRequestHandler<TransFerByIdCommand, SheepShowDto>,IRequestHandler<TransFerByIdEarTag,SheepRescission>
    {
        private readonly ITransferModellShowRepository transfer;
        private readonly DbHelper dbHelper;
        public TransferShowlCommondHandler(ITransferModellShowRepository _transfer,SqlDbContext _db,DbHelper _dbHelper)
        {
            this.transfer = _transfer;
            this.dbHelper = _dbHelper;
        }
        /// <summary>
        /// 转舍显示
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<Pager<SheepShowDto>> Handle(TransferShowlCommond request, CancellationToken cancellationToken)
        {
            if (request.RoundType == null)
            {
                request.RoundType = 0;
            }
            var sqlBuilder = new StringBuilder();
            sqlBuilder.Append(@"
                    SELECT 
                        sr.SheepRescissionId,
                        sr.EarTag,
                        sr.SurrenderDate,
                        sr.RoundType,
                        TOBM_Out.BuildingName AS TurnOutBuild,
                        CM_Out.ColumnName AS TurnOutHuts,
                        TOBM_In.BuildingName AS ShiftToBuild,
                        CM_In.ColumnName AS ShiftToHuts,
			            sl.Variety,
                        sr.CreateBy,
                        sr.CreateDate,
                        sr.UpdateBy,
                        sr.UpdateDate,
                        sr.DocumentNumbe,
			            sl.Sex,
			            sr.Type
                    FROM
                        SheepRescission sr
                        LEFT JOIN ColumnManagementModel TOBM_Out ON sr.TurnOutBuild = TOBM_Out.BuildingManagementId
                        LEFT JOIN FieldModel CM_Out ON sr.TurnOutHuts = CM_Out.ColumnManagementId
                        LEFT JOIN ColumnManagementModel TOBM_In ON sr.ShiftToBuild = TOBM_In.BuildingManagementId
                        LEFT JOIN FieldModel CM_In ON sr.ShiftToHuts = CM_In.ColumnManagementId
			            INNER JOIN SheepFileModel sl ON sl.EarTag = sr.EarTag
                    WHERE
                        1=1");

            if (request.SurrenderDate != null)
            {
                sqlBuilder.Append(" AND sr.SurrenderDate = @SurrenderDate ");
            }

            if (!string.IsNullOrEmpty(request.EarTag))
            {
                sqlBuilder.Append(" AND sr.EarTag LIKE '%' + @EarTag + '%'");
            }

            if (request.RoundType != 0)
            {
                sqlBuilder.Append(" AND sr.RoundType = @RoundType");
            }

            if (!string.IsNullOrEmpty(request.TurnOutBuild.ToString()))
            {
                sqlBuilder.Append(" AND (sr.TurnOutBuild = @TurnOutBuild)");
            }

            if (!string.IsNullOrEmpty(request.TurnOutHuts.ToString()))
            {
                sqlBuilder.Append(" AND (sr.TurnOutHuts = @TurnOutHuts)");
            }
            if (!string.IsNullOrEmpty(request.ShiftToBuild.ToString()))
            {
                sqlBuilder.Append(" AND (sr.ShiftToBuild = @ShiftToBuild)");
            }
            if (!string.IsNullOrEmpty(request.ShiftToHuts.ToString()))
            {
                sqlBuilder.Append(" AND (sr.ShiftToHuts = @ShiftToHuts)");
            }

            SqlParameter[] value =
            {
                new SqlParameter("@SurrenderDate", request.SurrenderDate ?? (object)DBNull.Value),
                new SqlParameter("@EarTag", request.EarTag ?? (object)DBNull.Value),
                new SqlParameter("@RoundType", request.RoundType ?? (object)DBNull.Value),
                new SqlParameter("@TurnOutBuild", request.TurnOutBuild ?? (object)DBNull.Value),
                new SqlParameter("@TurnOutHuts", request.TurnOutHuts ?? (object)DBNull.Value),
                new SqlParameter("@ShiftToBuild", request.ShiftToBuild ?? (object)DBNull.Value),
                new SqlParameter("@ShiftToHuts", request.ShiftToHuts ?? (object)DBNull.Value),
                new SqlParameter("@Offset", (request.PageIndex - 1) * request.PageSize),
                new SqlParameter("@PageSize", request.PageSize)
            };
            SqlParameter[] values =
            {
                new SqlParameter("@SurrenderDate", request.SurrenderDate ?? (object)DBNull.Value),
                new SqlParameter("@EarTag", request.EarTag ?? (object)DBNull.Value),
                new SqlParameter("@RoundType", request.RoundType ?? (object)DBNull.Value),
                new SqlParameter("@TurnOutBuild", request.TurnOutBuild ?? (object)DBNull.Value),
                new SqlParameter("@TurnOutHuts", request.TurnOutHuts ?? (object)DBNull.Value),
                new SqlParameter("@ShiftToBuild", request.ShiftToBuild ?? (object)DBNull.Value),
                new SqlParameter("@ShiftToHuts", request.ShiftToHuts ?? (object)DBNull.Value),
                new SqlParameter("@Offset", (request.PageIndex - 1) * request.PageSize),
                new SqlParameter("@PageSize", request.PageSize)
            };
            var listpage = await dbHelper.GetListDataBySql<SheepShowDto>(sqlBuilder.ToString(), value);

            if (request.PageIndex > 0 && request.PageSize > 0)
            {
                sqlBuilder.Append(" ORDER BY sr.EarTag, sr.SurrenderDate DESC"); // 添加排序条件

                // 追加分页查询
                sqlBuilder.Append(" OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY");
            }
            var listpages = await dbHelper.GetListDataBySql<SheepShowDto>(sqlBuilder.ToString(), values);
            int TotalCount = listpage.Count();
            // 计算总页数
            int PageCount = (int)Math.Ceiling((TotalCount * 1.0) / request.PageSize);
            
            return new Pager<SheepShowDto>()
            {
                TotalCount = TotalCount,
                PageCount = PageCount,
                resultData = listpages.OrderByDescending(x => x.SurrenderDate).ToList()
            };
        }

        /// <summary>
        /// 连接查询根据栋舍、转舍表
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<List<SheepFromDto>> Handle(TransferCommond request, CancellationToken cancellationToken)
        {
            var sql = @"SELECT
                        sr.SheepRescissionId,
                        cmo.BuildingName AS TurnOuBuildingManagementName,
                        cmm.ColumnName AS TurnOuColumnManagementName,
                        cm.BuildingName AS ShiftToBuildingManagementName,
                        c.ColumnName AS ShiftToColumnManagementName,
		                sr.SurrenderDate , 
		                sr.DocumentNumbe
    
                    FROM
                        SheepRescission sr
                        INNER JOIN ColumnManagementModel cmo ON sr.TurnOuBuildingManagementId = cmo.BuildingManagementId
                        INNER JOIN FieldModel cmm ON sr.TurnOuColumnManagementId = cmm.ColumnManagementId
                        INNER JOIN ColumnManagementModel cm ON sr.ShiftToBuildingManagementId = cm.BuildingManagementId
                        INNER JOIN FieldModel c ON sr.ShiftToColumnManagementId = c.ColumnManagementId
                    WHERE
                        sr.EarTag = @EarTag";
            SqlParameter[] value =
            {
                new SqlParameter("@EarTag",request.EarTag)
            };
            var list = await dbHelper.GetListDataBySql<SheepFromDto>(sql, value);
            return list;
        }

        public async Task<SheepRescission> Handle(TransFerByDateCommond request, CancellationToken cancellationToken)
        {
            string sql = "SELECT * FROM [dbo].[SheepRescission] WHERE EarTag=@EarTag ORDER  BY SurrenderDate DESC";
            SqlParameter[] value =
            {
                new SqlParameter("@EarTag",request.EarTag?? (object)DBNull.Value),
            };
            var list = await dbHelper.GetListDataBySql<SheepRescission>(sql, value);
            
            // 获取第一个元素或按需要进行筛选
            var result = list.FirstOrDefault();
            return result;
        }
        /// <summary>
        /// 根据耳标返填栋舍栏位
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        public async Task<SheepShowDto> Handle(TransFerByIdCommand request, CancellationToken cancellationToken)
        {
            string sql = "SELECT a.*,b.Variety AS riety FROM SheepRescission a LEFT JOIN SheepFileModel b ON a.EarTag=b.EarTag WHERE a.EarTag=@EarTag ORDER BY SurrenderDate DESC";
            SqlParameter[] value =
            {
                new SqlParameter("@EarTag",request.EarTag?? (object)DBNull.Value),
            };
            var list = await dbHelper.GetListDataBySql<SheepShowDto>(sql, value);
            // 获取第一个元素或按需要进行筛选
            var result = list.FirstOrDefault();
            return result;
        }
        /// <summary>
        /// 转舍返填
        /// </summary>
        /// <param name="request"></param>
        /// <param name="cancellationToken"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<SheepRescission> Handle(TransFerByIdEarTag request, CancellationToken cancellationToken)
        {
            string sql = "SELECT TOP(1) * FROM SheepRescission WHERE EarTag=@EarTag ORDER BY SurrenderDate DESC";
            SqlParameter[] value =
            {
                new SqlParameter("@EarTag",request.EarTag?? (object)DBNull.Value),
            };
            var list = await dbHelper.GetListDataBySql<SheepRescission>(sql, value);
            var result = list.FirstOrDefault();
            if(result.SurrenderDate==request.SurrenderDate)
            {
                if(result.TransFerType==0)
                {
                    return new SheepRescission {
                        CreateBy = request.EarTag + "为事件转舍、无法修改"
                    };
                }
                else { 
                    return result;
                }
            }
            else
            {
                return new SheepRescission
                {
                    CreateBy = "当前数据不是最新数据"
                };
            }
        }
    }
}
